package com.kevin.executors;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.kevin.action.JdbcParams;
import com.kevin.exceptions.BaseException;
import com.kevin.exceptions.ExceptionFactory;
import com.kevin.session.Configuration;
import com.kevin.table.MCellStruct;
import com.kevin.table.MDataColumn;
import com.kevin.transaction.Transaction;

public class BaseExecutor implements Executor {
	private static final Logger LOGGER = LoggerFactory.getLogger(BaseExecutor.class);
	protected Transaction transaction;
	protected Configuration configuration;
	private boolean closed;

	protected BaseExecutor(Configuration configuration, Transaction transaction) {
		this.transaction = transaction;
		this.closed = false;
		this.configuration = configuration;
	}

	@Override
	public Transaction getTransaction() {
		if (closed) {
			throw new BaseException("Executor was closed.");
		}
		return transaction;
	}

	@Override
	public void close(boolean forceRollback) {
		try {
			try {
				rollback(forceRollback);
			} finally {
				if (transaction != null) {
					transaction.close();
				}
			}
		} catch (SQLException e) {
			// Ignore. There's nothing that can be done at this point.
			LOGGER.warn("Unexpected exception on closing transaction.  Cause: " + e);
		} finally {
			transaction = null;
			closed = true;
		}

	}

	@Override
	public boolean isClosed() {
		return closed;
	}


	@Override
	public void commit(boolean required) throws SQLException {
		if (closed) {
			throw new BaseException("Cannot commit, transaction is already closed");
		}
		if (required) {
			transaction.commit();
		}
	}

	@Override
	public void rollback(boolean required) throws SQLException {
		if (!closed) {

			if (required) {
				transaction.rollback();
			}

		}
	}

	@Override
	public ResultSet query(String sql, JdbcParams[] paramList) {
		try {
			PreparedStatement pstmt = getConnection().prepareStatement(sql);
			setQueryParameters(pstmt, paramList);
			LOGGER.debug("query: " + pstmt.toString().split(":")[1]);
			return pstmt.executeQuery();
		} catch (SQLException e) {
			close(true);
			String msg = "failed to execute query sql!";
			throw new ExecutorException(msg);
		}
	}

	/**
	 * 新增，删除，修改
	 * 
	 * @param sql
	 * @return
	 */
	@Override
	public int update(String sql, JdbcParams[] paramList) {
		try {
			PreparedStatement pstmt = getConnection().prepareStatement(sql);
			setQueryParameters(pstmt, paramList);
			LOGGER.debug("update: " + pstmt.toString().split(":")[1]);
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			close(true);
			String msg = "failed to execute update sql!";
			throw new ExecutorException(msg,e);
		}
	}

	@Override
	public int updateBatch(String sql, JdbcParams[] paramList, int onceCount) {
		try {
			int sum = 0;
			PreparedStatement pstmt = getConnection().prepareStatement(sql);
			if (paramList.length > 0) {
				JdbcParams param = null;
				System.out.println(paramList);
				for (int i = 0; i < paramList.length; i++) {
					param = paramList[i];
					try {
						int count = i % onceCount;
						if (i > 0 && count == 0) {
							pstmt.addBatch();
						}
						pstmt.setObject(count + 1, param.getValue(), param.getJdbcType().TYPE_CODE);

						if (i > 0 && (i + 1) / onceCount > 1000) {
							LOGGER.debug("updateBatch: " + pstmt.toString().split(":")[1]);
							pstmt.addBatch();
							int[] retCount = pstmt.executeBatch();
							pstmt.clearBatch();
							for (int j : retCount) {
								sum += j;
							}
						}
					} catch (SQLException e) {
						String msg = "failed to add parameters to PreparedStatement!";
						throw new ExecutorException(msg,e);
					}
				}
			}
			LOGGER.debug("updateBatch: " + pstmt.toString().split(":")[1]);
			pstmt.addBatch();
			int[] retCount = pstmt.executeBatch();
			for (int i : retCount) {
				sum += i;
			}
			return sum;
		} catch (SQLException e) {
			close(true);
			String msg = "failed to execute updateBatch sql!";
			throw new ExecutorException(msg,e);
		}
	}

	/**
	 * 为查询条件设置参数
	 * 
	 * @param pstmt
	 */
	private void setQueryParameters(PreparedStatement pstmt, JdbcParams[] paramList) {
		if (paramList == null || paramList.length == 0)
			return;

		JdbcParams param = null;
		for (int i = 0; i < paramList.length; i++) {
			param = paramList[i];
			try {
				pstmt.setObject(i + 1, param.getValue(), param.getJdbcType().TYPE_CODE);
			} catch (SQLException e) {
				String msg = "failed to add parameters to PreparedStatement!";
				throw new ExecutorException(msg);
			}
		}
	}

	/**
	 * 获取所有列
	 * 
	 * @param tableName
	 * @param dbHelper
	 * @return
	 * @throws SQLException
	 */
	@Override
	public MDataColumn getColumns(String tableName) throws SQLException {
		MDataColumn mdcs = new MDataColumn();
		boolean isView = tableName.startsWith("select ") || (tableName.contains(" ") && tableName.charAt(0) != '(');
		if (isView) {
			mdcs = getViewColumns(tableName);
		} else {
			MCellStruct mStruct = null;
			mdcs.setDbType(configuration.getDbType());
			Map<String, Map<String, Object>> colInfo = getColumns(getConnection().getMetaData(), tableName);
			Map<String, Object> colProp = null;
			for (String key : colInfo.keySet()) {
				colProp = colInfo.get(key);
				mStruct = new MCellStruct(mdcs.getDbType());
				mStruct.setColumnName((String) colProp.get("columnName"));
				mStruct.setSqlType((int) colProp.get("sqlType"));
				mStruct.setDescription((String) colProp.get("description"));
				mStruct.setDefaultValue((String) colProp.get("defaultValue"));
				mStruct.setCanNull((boolean) colProp.get("isCanNull"));
				mStruct.setAutoIncrement((boolean) colProp.get("isAutoIncrement"));
				mStruct.setTableName((String) colProp.get("tableName"));
				mStruct.setMaxSize((int) colProp.get("maxSize"));
				mStruct.setScale((int) colProp.get("scale"));
				mStruct.setPrimaryKey((boolean) colProp.get("isPrimaryKey"));
				mStruct.setTypeName((String) colProp.get("sqlTypeName"));
				mStruct.setForeignKey((boolean) colProp.get("isForeignKey"));
				mStruct.setFkTableName((String) colProp.get("fkTableName"));
				mStruct.setUniqueKey((boolean) colProp.get("isUniqueKey"));
				mdcs.add(mStruct);
			}
		}

		return mdcs;
	}

	private MDataColumn getViewColumns(String sqlText) throws SQLException {
		PreparedStatement pstmt = getConnection().prepareStatement(sqlText);
		ResultSetMetaData metaData = pstmt.getMetaData();
		MDataColumn mdc = getColumns(metaData);
		mdc.setDbType(configuration.getDbType());
		return mdc;
	}

	private MDataColumn getColumns(ResultSetMetaData metaData) throws SQLException {
		int columnCount = metaData.getColumnCount();
		if (columnCount == 0)
			return null;
		MCellStruct mStruct = null;
		MDataColumn mdcs = new MDataColumn();
		for (int i = 0; i < columnCount; i++) {
			String columnName = metaData.getColumnName(i);
			int sqlType = metaData.getColumnType(i);
			boolean isAutoIncrement = metaData.isAutoIncrement(i);
			boolean isCanNull = metaData.isNullable(i) == ResultSetMetaData.columnNullable;
			int maxSize = metaData.getColumnDisplaySize(i);
			mStruct = new MCellStruct(columnName, sqlType, isAutoIncrement, isCanNull, maxSize);
			mStruct.setScale(metaData.getScale(i));
			mStruct.setTypeName(metaData.getColumnTypeName(i));
			mStruct.setTableName(metaData.getTableName(i));
			mdcs.add(mStruct);
		}
		return mdcs;
	}

	/**
	 * 获取所有列
	 * 
	 * @param metaData
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	private Map<String, Map<String, Object>> getColumns(DatabaseMetaData metaData, String tableName)
			throws SQLException {
		Map<String, Map<String, Object>> columnProps = new HashMap<>();
		Map<String, Object> colProp = null;
		ResultSet rs = metaData.getColumns(null, null, tableName, null);
		Map<String, Boolean> pKeys = getPrimaryKeys(metaData, tableName);
		Map<String, String> fKeys = getForeignKeys(metaData, tableName);
		Map<String, Boolean> uniKeys = getUniqueKeys(metaData, tableName);
		while (rs.next()) {
			colProp = new HashMap<>();
			colProp.put("sqlType", rs.getInt("DATA_TYPE"));// 对应的java.sql.Types的SQL类型(列类型ID)
			colProp.put("description", rs.getString("REMARKS"));// 列描述
			colProp.put("defaultValue", rs.getString("COLUMN_DEF"));// 默认值
			colProp.put("isCanNull", rs.getInt("NULLABLE") == 0 ? false : true);// 是否允许为null
			colProp.put("isAutoIncrement", rs.getBoolean("IS_AUTOINCREMENT"));// 指示此列是否是自动递增
			colProp.put("columnName", rs.getString("COLUMN_NAME"));// 列名
			colProp.put("tableName", rs.getString("TABLE_NAME")); // 表名
			colProp.put("maxSize", rs.getInt("COLUMN_SIZE"));// 列大小
			colProp.put("scale", rs.getInt("DECIMAL_DIGITS")); // 小数位数
			colProp.put("isPrimaryKey", pKeys.get(colProp.get("columnName")) == null ? false : true);
			colProp.put("sqlTypeName", rs.getString("TYPE_NAME"));// java.sql.Types类型名称(列类型名称)
			colProp.put("isForeignKey", fKeys.containsKey(colProp.get("columnName")));
			colProp.put("fkTableName", fKeys.get(colProp.get("columnName")));
			colProp.put("isUniqueKey",
					uniKeys.containsKey(colProp.get("columnName")) ? uniKeys.get(colProp.get("columnName")) : false);
			columnProps.put((String) colProp.get("columnName"), colProp);
		}
		return columnProps;
	}

	/**
	 * 获取主键列名
	 * 
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	private Map<String, Boolean> getPrimaryKeys(DatabaseMetaData metaData, String tableName) throws SQLException {
		Map<String, Boolean> primaryMap = new HashMap<>();
		ResultSet rs = metaData.getPrimaryKeys(null, null, tableName);
		while (rs.next()) {
			primaryMap.put(rs.getString("COLUMN_NAME"), true);
		}
		return primaryMap;
	}

	/**
	 * 获取表的外键
	 * 
	 * @param metaData
	 * @param tableName
	 * @return<外键列名称，外键表名称>
	 * @throws SQLException
	 */
	private Map<String, String> getForeignKeys(DatabaseMetaData metaData, String tableName) throws SQLException {
		Map<String, String> foreignMap = new HashMap<>();
		ResultSet rs = metaData.getImportedKeys(null, null, tableName);
		while (rs.next()) {
			foreignMap.put(rs.getString("FKCOLUMN_NAME"), rs.getString("PKTABLE_NAME"));
		}
		return foreignMap;
	}

	private Map<String, Boolean> getUniqueKeys(DatabaseMetaData metaData, String tableName) throws SQLException {
		Map<String, Boolean> uniqueMap = new HashMap<>();
		ResultSet rs = metaData.getIndexInfo(null, null, tableName, true, true);
		while (rs.next()) {
			uniqueMap.put(rs.getString("COLUMN_NAME"), !rs.getBoolean("NON_UNIQUE"));
		}
		return uniqueMap;
	}

	@Override
	public Map<String, String> getTables() {
		Map<String, String> tableMap = new HashMap<>();
		String[] types = { "TABLE", "VIEW" };
		try {
			DatabaseMetaData metaData = getConnection().getMetaData();
			// 数据库名称，数据库登录名，表名称，类型标准（"TABLE"、"VIEW"、"SYSTEM TABLE"、"GLOBAL
			// TEMPORARY"、"LOCAL TEMPORARY"、"ALIAS" 和 "SYNONYM"）
			ResultSet rs = metaData.getTables(null, null, null, types);
			while (rs.next()) {
				String tableName = rs.getString("TABLE_NAME"); // 表名
				// String tableType = rs.getString("TABLE_TYPE"); //表类型
				String remarks = rs.getString("REMARKS"); // 表备注
				tableMap.put(tableName, remarks);
			}
		} catch (SQLException e) {
			throw ExceptionFactory.Exception("get tableinfo failed !");
		}
		return tableMap;
	}

	@Override
	public boolean isPrimaryKey(String tableName, String columnName) throws SQLException {
		DatabaseMetaData metaData = getConnection().getMetaData();
		Map<String, Boolean> pKeys = getPrimaryKeys(metaData, tableName);
		return pKeys.get(columnName) == null ? false : true;
	}

	protected Connection getConnection() throws SQLException {
		Connection connection = transaction.getConnection();
		return connection;
	}

}
